﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repository
{
    public class TransferOrderRepository : ITransferOrderRepository
    {
        private readonly IDbConnection _dbConnection;
        private readonly ILogger<TransferOrderRepository> _logger;

        public TransferOrderRepository(IDbConnection dbConnection, ILogger<TransferOrderRepository> logger)
        {
            _dbConnection = dbConnection ?? throw new ArgumentNullException(nameof(dbConnection));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        }

        /// <summary>
        /// 分页查询调拨单列表（修复多条件查询）
        /// </summary>
        public async Task<List<TransferOrderDTO>> GetTransferOrdersAsync(
      string transnoNo, string documentStatus, string ioType, string ioStatus,
      DateTime? startDate, DateTime? endDate, int pageIndex = 1, int pageSize = 10)
        {
            // 分页参数校正
            pageIndex = pageIndex < 1 ? 1 : pageIndex;
            pageSize = pageSize < 1 || pageSize > 100 ? 10 : pageSize;
            int offset = (pageIndex - 1) * pageSize;

            _logger.LogDebug("Repository接收查询参数：transnoNo={TransnoNo}, documentStatus={DocumentStatus}, " +
                "ioType={IoType}, ioStatus={IoStatus}, startDate={StartDate}, endDate={EndDate}, " +
                "pageIndex={PageIndex}, pageSize={PageSize}",
                transnoNo, documentStatus, ioType, ioStatus, startDate?.ToString("yyyy-MM-dd"),
                endDate?.ToString("yyyy-MM-dd HH:mm:ss"), pageIndex, pageSize);

            // 构建SQL（完整字段查询，确保JOIN逻辑正确）
            var sqlBuilder = new StringBuilder(@"
SELECT 
    tor.transno_no AS TransnoNo,
    tor.document_status AS DocumentStatus,
    tor.tfrrqdt AS Tfrrqdt,
    tor.create_time AS CreateTime,
    tor.transfer_type AS TransferType,
    ISNULL(outWh.warehouse_name, tor.outwhse) AS OutWarehouseName,
    ISNULL(inWh.warehouse_name, tor.inwhse) AS InWarehouseName,
    ISNULL(outWh.warehouse_number, '') AS OutWarehouseCode,
    ISNULL(inWh.warehouse_number, '') AS InWarehouseCode,
    tor.outbound_status AS OutboundStatus, -- 数据库char(1)类型
    tor.storage_status AS StorageStatus,   -- 数据库char(1)类型
    ISNULL((SELECT SUM(TRY_CAST(transfer_quantity AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferQuantity,
    ISNULL((SELECT SUM(TRY_CAST(transfer_amount AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferAmount,
    ISNULL(handler.user_name, tor.handler_id) AS HandlerName,
    tor.create_time AS MakeOrderTime,
    ISNULL((SELECT TOP 1 ISNULL(p.product_name, td.product_code) 
            FROM wms_knzy_transfer_details td
            LEFT JOIN wms_basic_product_information p ON td.product_code = p.product_code
            WHERE td.transfer_order_id = tor.transfer_order_id), '无货品') AS ProductName,
    ISNULL(maker.user_name, tor.create_by) AS MakerName,
    ISNULL(dept.organization_name, '-') AS DepartmentName,
    ISNULL(
        (SELECT user_name FROM sys_org_user_management WHERE user_account = tor.reviewer),
        ISNULL(tor.reviewer, '-')
    ) AS ReviewerName,
    tor.review_time AS ReviewTime,
    tor.transfer_order_id AS TransferOrderId,
    TRY_CAST(tor.outwhse AS INT) AS OutWarehouseId,
    TRY_CAST(tor.inwhse AS INT) AS InWarehouseId
FROM wms_knzy_transfer_order tor
LEFT JOIN wms_basic_warehouse outWh ON TRY_CAST(tor.outwhse AS INT) = outWh.warehouse_id  
LEFT JOIN wms_basic_warehouse inWh ON TRY_CAST(tor.inwhse AS INT) = inWh.warehouse_id    
LEFT JOIN sys_org_user_management handler ON TRY_CAST(tor.handler_id AS INT) = handler.user_id
LEFT JOIN sys_org_user_management maker ON tor.create_by = maker.user_account
LEFT JOIN sys_org_organizations dept ON TRY_CAST(maker.organization_id AS INT) = dept.organization_id
WHERE tor.is_delete = 0 -- 基础条件：未删除
");

            var parameters = new DynamicParameters();

            // 1. 调拨单号（模糊查询，去空格）
            if (!string.IsNullOrWhiteSpace(transnoNo))
            {
                string trimmedNo = transnoNo.Trim();
                sqlBuilder.AppendLine(" AND tor.transno_no LIKE @TransnoNo");
                parameters.Add("@TransnoNo", $"%{trimmedNo}%");
                _logger.LogDebug("拼接调拨单号条件：tor.transno_no LIKE '%{TrimmedNo}%'", trimmedNo);
            }

            // 2. 单据状态（精确匹配，字符串类型）
            if (!string.IsNullOrWhiteSpace(documentStatus))
            {
                string trimmedStatus = documentStatus.Trim();
                sqlBuilder.AppendLine(" AND tor.document_status = @DocumentStatus");
                parameters.Add("@DocumentStatus", trimmedStatus);
                _logger.LogDebug("拼接单据状态条件：tor.document_status = '{TrimmedStatus}'", trimmedStatus);
            }

            // 3. 日期范围条件（闭区间匹配）
            if (startDate.HasValue)
            {
                sqlBuilder.AppendLine(" AND tor.tfrrqdt >= @StartDate");
                parameters.Add("@StartDate", startDate.Value.Date);
                _logger.LogDebug("拼接开始日期条件：tor.tfrrqdt >= {StartDate}", startDate.Value.Date.ToString("yyyy-MM-dd"));
            }
            if (endDate.HasValue)
            {
                sqlBuilder.AppendLine(" AND tor.tfrrqdt <= @EndDate");
                parameters.Add("@EndDate", endDate.Value);
                _logger.LogDebug("拼接结束日期条件：tor.tfrrqdt <= {EndDate}", endDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            // 4. 🔴 核心修复：出入库状态条件（匹配数据库char(1)类型）
            if (!string.IsNullOrWhiteSpace(ioType) && !string.IsNullOrWhiteSpace(ioStatus))
            {
                string trimmedIoType = ioType.Trim().ToLower();
                string trimmedIoStatus = ioStatus.Trim();

                if (trimmedIoType == "out")
                {
                    // 出库状态：匹配char(1)字段，字符串参数
                    sqlBuilder.AppendLine(" AND tor.outbound_status = @OutboundStatus");
                    parameters.Add("@OutboundStatus", trimmedIoStatus);
                    _logger.LogDebug("拼接触发库状态条件：tor.outbound_status = '{TrimmedIoStatus}'", trimmedIoStatus);
                }
                else if (trimmedIoType == "in")
                {
                    // 入库状态：匹配char(1)字段，字符串参数
                    sqlBuilder.AppendLine(" AND tor.storage_status = @StorageStatus");
                    parameters.Add("@StorageStatus", trimmedIoStatus);
                    _logger.LogDebug("拼接入库状态条件：tor.storage_status = '{TrimmedIoStatus}'", trimmedIoStatus);
                }
                else
                {
                    _logger.LogWarning("无效的出入库类型，忽略该条件：{TrimmedIoType}", trimmedIoType);
                }
            }

            // 5. 分页逻辑（SQL Server语法）
            sqlBuilder.AppendLine(" ORDER BY tor.create_time DESC OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
            parameters.Add("@Offset", offset);
            parameters.Add("@PageSize", pageSize);
            _logger.LogDebug("拼接分页条件：OFFSET {Offset} ROWS FETCH NEXT {PageSize} ROWS ONLY", offset, pageSize);

            // 打印最终SQL和参数（调试关键）
            string finalSql = sqlBuilder.ToString();
            _logger.LogDebug("最终查询SQL：\n{FinalSql}", finalSql);
            _logger.LogDebug("绑定的SQL参数：{@Parameters}", parameters.ParameterNames.ToDictionary(name => name, name => parameters.Get<object>(name)));

            try
            {
                // 确保数据库连接打开（改用同步方法）
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }

                // 执行查询
                var result = await _dbConnection.QueryAsync<TransferOrderDTO>(finalSql, parameters);
                var resultList = result?.AsList() ?? new List<TransferOrderDTO>();
                _logger.LogDebug("查询执行完成，返回记录数：{ResultCount}", resultList.Count);
                return resultList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "执行调拨单查询失败，SQL：{FinalSql}，参数：{@Parameters}", finalSql, parameters);
                throw new InvalidOperationException("查询调拨单列表异常", ex);
            }
            finally
            {
                // 确保数据库连接关闭（改用同步方法）
                if (_dbConnection.State == ConnectionState.Open)
                {
                    _dbConnection.Close();
                }
            }
        }

        public async Task<int> GetTransferOrdersCountAsync(
            string transnoNo, string documentStatus, string ioType, string ioStatus,
            DateTime? startDate, DateTime? endDate)
        {
            _logger.LogDebug("Repository接收总数查询参数：transnoNo={TransnoNo}, documentStatus={DocumentStatus}, " +
                "ioType={IoType}, ioStatus={IoStatus}, startDate={StartDate}, endDate={EndDate}",
                transnoNo, documentStatus, ioType, ioStatus, startDate?.ToString("yyyy-MM-dd"),
                endDate?.ToString("yyyy-MM-dd HH:mm:ss"));

            // 构建总数查询SQL（与列表查询条件完全一致）
            var sqlBuilder = new StringBuilder(@"
SELECT COUNT(tor.transfer_order_id) AS TotalCount
FROM wms_knzy_transfer_order tor
LEFT JOIN wms_basic_warehouse outWh ON TRY_CAST(tor.outwhse AS INT) = outWh.warehouse_id  
LEFT JOIN wms_basic_warehouse inWh ON TRY_CAST(tor.inwhse AS INT) = inWh.warehouse_id    
LEFT JOIN sys_org_user_management handler ON TRY_CAST(tor.handler_id AS INT) = handler.user_id
LEFT JOIN sys_org_user_management maker ON tor.create_by = maker.user_account
LEFT JOIN sys_org_organizations dept ON TRY_CAST(maker.organization_id AS INT) = dept.organization_id
WHERE tor.is_delete = 0 -- 基础条件：未删除
");

            var parameters = new DynamicParameters();

            // 1. 调拨单号（与列表查询一致）
            if (!string.IsNullOrWhiteSpace(transnoNo))
            {
                string trimmedNo = transnoNo.Trim();
                sqlBuilder.AppendLine(" AND tor.transno_no LIKE @TransnoNo");
                parameters.Add("@TransnoNo", $"%{trimmedNo}%");
                _logger.LogDebug("总数查询-拼接调拨单号条件：tor.transno_no LIKE '%{TrimmedNo}%'", trimmedNo);
            }

            // 2. 单据状态（与列表查询一致）
            if (!string.IsNullOrWhiteSpace(documentStatus))
            {
                string trimmedStatus = documentStatus.Trim();
                sqlBuilder.AppendLine(" AND tor.document_status = @DocumentStatus");
                parameters.Add("@DocumentStatus", trimmedStatus);
                _logger.LogDebug("总数查询-拼接单据状态条件：tor.document_status = '{TrimmedStatus}'", trimmedStatus);
            }

            // 3. 日期范围（与列表查询一致）
            if (startDate.HasValue)
            {
                sqlBuilder.AppendLine(" AND tor.tfrrqdt >= @StartDate");
                parameters.Add("@StartDate", startDate.Value.Date);
                _logger.LogDebug("总数查询-拼接开始日期条件：tor.tfrrqdt >= {StartDate}", startDate.Value.Date.ToString("yyyy-MM-dd"));
            }
            if (endDate.HasValue)
            {
                sqlBuilder.AppendLine(" AND tor.tfrrqdt <= @EndDate");
                parameters.Add("@EndDate", endDate.Value);
                _logger.LogDebug("总数查询-拼接结束日期条件：tor.tfrrqdt <= {EndDate}", endDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            // 4. 🔴 核心修复：出入库状态条件（与列表查询完全一致）
            if (!string.IsNullOrWhiteSpace(ioType) && !string.IsNullOrWhiteSpace(ioStatus))
            {
                string trimmedIoType = ioType.Trim().ToLower();
                string trimmedIoStatus = ioStatus.Trim();

                if (trimmedIoType == "out")
                {
                    sqlBuilder.AppendLine(" AND tor.outbound_status = @OutboundStatus");
                    parameters.Add("@OutboundStatus", trimmedIoStatus);
                    _logger.LogDebug("总数查询-拼接触发库状态条件：tor.outbound_status = '{TrimmedIoStatus}'", trimmedIoStatus);
                }
                else if (trimmedIoType == "in")
                {
                    sqlBuilder.AppendLine(" AND tor.storage_status = @StorageStatus");
                    parameters.Add("@StorageStatus", trimmedIoStatus);
                    _logger.LogDebug("总数查询-拼接入库状态条件：tor.storage_status = '{TrimmedIoStatus}'", trimmedIoStatus);
                }
                else
                {
                    _logger.LogWarning("总数查询-无效的出入库类型，忽略该条件：{TrimmedIoType}", trimmedIoType);
                }
            }

            // 打印总数查询SQL和参数
            string finalCountSql = sqlBuilder.ToString();
            _logger.LogDebug("最终总数查询SQL：\n{FinalCountSql}", finalCountSql);
            _logger.LogDebug("总数查询绑定参数：{@Parameters}", parameters.ParameterNames.ToDictionary(name => name, name => parameters.Get<object>(name)));

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }

                // 执行总数查询
                int totalCount = await _dbConnection.ExecuteScalarAsync<int>(finalCountSql, parameters);
                _logger.LogDebug("总数查询完成，返回总记录数：{TotalCount}", totalCount);
                return totalCount;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "执行调拨单总数查询失败，SQL：{FinalCountSql}，参数：{@Parameters}", finalCountSql, parameters);
                throw new InvalidOperationException("查询调拨单总数异常", ex);
            }
            finally
            {
                if (_dbConnection.State == ConnectionState.Open)
                {
                    _dbConnection.Close();
                }
            }
        }

        public async Task<bool> DeleteAsync(int transferOrderId)
        {
            if (transferOrderId <= 0)
                throw new ArgumentException("调拨单ID必须大于0");

            var checkSql = "SELECT document_status FROM wms_knzy_transfer_order WHERE transfer_order_id = @Id";
            EnsureConnectionOpen();
            var status = await _dbConnection.QueryFirstOrDefaultAsync<string>(checkSql, new { Id = transferOrderId });
            if (status != "0" && status != "1" && status != "2")
            {
                _logger.LogWarning($"删除失败：调拨单{transferOrderId}状态为{status}（仅草稿/待审核/驳回可删除）");
                return false;
            }

            IDbTransaction transaction = null;
            var wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
            try
            {
                EnsureConnectionOpen();
                transaction = _dbConnection.BeginTransaction();

                await _dbConnection.ExecuteAsync(
                    "DELETE FROM wms_knzy_transfer_details WHERE transfer_order_id = @Id",
                    new { Id = transferOrderId }, transaction);

                var mainRows = await _dbConnection.ExecuteAsync(
                    "DELETE FROM wms_knzy_transfer_order WHERE transfer_order_id = @Id",
                    new { Id = transferOrderId }, transaction);

                transaction.Commit();
                return mainRows > 0;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                _logger.LogError(ex, $"删除调拨单{transferOrderId}失败");
                return false;
            }
            finally
            {
                transaction?.Dispose();
                if (!wasConnectionOpen) EnsureConnectionClosed();
            }
        }

        public async Task<bool> BatchDeleteAsync(List<int> ids)
        {
            if (ids == null || ids.Count == 0)
                throw new ArgumentException("至少选择一条调拨单");
            if (ids.Any(id => id <= 0))
                throw new ArgumentException("调拨单ID必须大于0");

            var checkSql = "SELECT COUNT(1) FROM wms_knzy_transfer_order WHERE transfer_order_id IN @Ids AND document_status NOT IN ('0','1','2')";
            EnsureConnectionOpen();
            var invalidCount = await _dbConnection.ExecuteScalarAsync<int>(checkSql, new { Ids = ids });
            if (invalidCount > 0)
            {
                _logger.LogWarning("批量删除失败：包含非草稿/待审核/驳回状态的单据");
                return false;
            }

            IDbTransaction transaction = null;
            var wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
            try
            {
                EnsureConnectionOpen();
                transaction = _dbConnection.BeginTransaction();

                await _dbConnection.ExecuteAsync(
                    "DELETE FROM wms_knzy_transfer_details WHERE transfer_order_id IN @Ids",
                    new { Ids = ids }, transaction);

                var mainRows = await _dbConnection.ExecuteAsync(
                    "DELETE FROM wms_knzy_transfer_order WHERE transfer_order_id IN @Ids",
                    new { Ids = ids }, transaction);

                transaction.Commit();
                return mainRows > 0;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                _logger.LogError(ex, "批量删除调拨单失败");
                return false;
            }
            finally
            {
                transaction?.Dispose();
                if (!wasConnectionOpen) EnsureConnectionClosed();
            }
        }

       

        public async Task<List<StorageLocationEntity>> GetAllStorageLocationsAsync()
        {
            var sql = @"SELECT location_id, location_name, location_number, warehouse_id
                FROM wms_basic_storage_location 
                WHERE is_delete = 0 AND location_status = 1
                ORDER BY sorting, location_name";

            try
            {
                EnsureConnectionOpen();
                var locations = (await _dbConnection.QueryAsync<StorageLocationEntity>(sql)).AsList();
                return locations;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询存储位置失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }

        public async Task<List<StorageLocationEntity>> GetStorageLocationsByWarehouseAsync(int warehouseId)
        {
            var sql = @"SELECT sl.location_id, sl.location_name, sl.location_number, sl.warehouse_id
                FROM wms_basic_storage_location sl
                INNER JOIN wms_basic_warehouse w ON sl.warehouse_id = w.warehouse_id
                WHERE sl.warehouse_id = @WarehouseId 
                  AND sl.is_delete = 0 
                  AND sl.location_status = 1
                ORDER BY sl.sorting, sl.location_name";

            try
            {
                EnsureConnectionOpen();
                var locations = (await _dbConnection.QueryAsync<StorageLocationEntity>(sql, new { WarehouseId = warehouseId })).AsList();
                return locations;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询仓库{warehouseId}的存储位置失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }

        public async Task<TransferOrderDTO> GetByIdAsync(int id)
        {
            if (id <= 0)
                throw new ArgumentException("调拨单ID必须大于0");

            var sql = @"
SELECT 
    tor.transfer_order_id AS TransferOrderId,
    tor.transno_no AS TransnoNo,
    tor.document_status AS DocumentStatus,
    tor.transfer_type AS TransferType,
    tor.tfrrqdt AS Tfrrqdt,
    TRY_CAST(tor.outwhse AS INT) AS OutWarehouseId,
    TRY_CAST(tor.inwhse AS INT) AS InWarehouseId,
    ISNULL(outWh.warehouse_name, tor.outwhse) AS OutWarehouseName,
    ISNULL(inWh.warehouse_name, tor.inwhse) AS InWarehouseName,
    -- 新增：仓库编码（warehouse_number）
    ISNULL(outWh.warehouse_number, '') AS OutWarehouseCode,
    ISNULL(inWh.warehouse_number, '') AS InWarehouseCode,
    ISNULL(handler.user_name, tor.handler_id) AS HandlerName,
    tor.note AS Note,
    tor.outbound_status AS OutboundStatus,
    tor.storage_status AS StorageStatus,
    ISNULL(maker.user_name, tor.create_by) AS MakerName,
    -- 关键修改：关联部门表获取部门名称
    ISNULL(dept.organization_name, '-') AS DepartmentName,
    tor.create_time AS CreateTime,
    tor.update_by AS UpdateBy,
    tor.update_time AS UpdateTime,
    ISNULL((SELECT user_name FROM sys_org_user_management WHERE user_account = tor.reviewer), ISNULL(tor.reviewer, '-')) AS ReviewerName,
    tor.review_time AS ReviewTime,
    tor.organizations_id AS DepartmentId,
    ISNULL((SELECT SUM(TRY_CAST(transfer_quantity AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferQuantity,
    ISNULL((SELECT SUM(TRY_CAST(transfer_amount AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS transferAmount
FROM wms_knzy_transfer_order tor
LEFT JOIN wms_basic_warehouse outWh ON TRY_CAST(tor.outwhse AS INT) = outWh.warehouse_id
LEFT JOIN wms_basic_warehouse inWh ON TRY_CAST(tor.inwhse AS INT) = inWh.warehouse_id
LEFT JOIN sys_org_user_management handler ON TRY_CAST(tor.handler_id AS INT) = handler.user_id
LEFT JOIN sys_org_user_management maker ON tor.create_by = maker.user_account
-- 关键修改：新增部门表关联
LEFT JOIN sys_org_organizations dept ON TRY_CAST(maker.organization_id AS INT) = dept.organization_id
WHERE tor.transfer_order_id = @Id AND tor.is_delete = 0";

            try
            {
                EnsureConnectionOpen();
                var order = await _dbConnection.QueryFirstOrDefaultAsync<TransferOrderDTO>(sql, new { Id = id });
                if (order != null)
                    order.details = await GetDetailsByOrderIdAsync(id);
                return order;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询调拨单{id}详情失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }
        /// <summary>
        /// 带事务的调拨单查询（核心修复）
        /// </summary>
        public async Task<TransferOrderDTO> GetByIdAsync(int id, DbTransaction transaction)
        {
            if (id <= 0)
                throw new ArgumentException("调拨单ID必须大于0");

            var sql = @"
SELECT 
    tor.transfer_order_id AS TransferOrderId,
    tor.transno_no AS TransnoNo,
    tor.document_status AS DocumentStatus,
    tor.transfer_type AS TransferType,
    tor.tfrrqdt AS Tfrrqdt,
    TRY_CAST(tor.outwhse AS INT) AS OutWarehouseId,
    TRY_CAST(tor.inwhse AS INT) AS InWarehouseId,
    ISNULL(outWh.warehouse_name, tor.outwhse) AS OutWarehouseName,
    ISNULL(inWh.warehouse_name, tor.inwhse) AS InWarehouseName,
    ISNULL(outWh.warehouse_number, '') AS OutWarehouseCode,
    ISNULL(inWh.warehouse_number, '') AS InWarehouseCode,
    ISNULL(handler.user_name, tor.handler_id) AS HandlerName,
    tor.note AS Note,
    tor.outbound_status AS OutboundStatus,
    tor.storage_status AS StorageStatus,
    ISNULL(maker.user_name, tor.create_by) AS MakerName,
    ISNULL(dept.organization_name, '-') AS DepartmentName,
    tor.create_time AS CreateTime,
    tor.update_by AS UpdateBy,
    tor.update_time AS UpdateTime,
    ISNULL((SELECT user_name FROM sys_org_user_management WHERE user_account = tor.reviewer), ISNULL(tor.reviewer, '-')) AS ReviewerName,
    tor.review_time AS ReviewTime,
    tor.organizations_id AS DepartmentId,
    ISNULL((SELECT SUM(TRY_CAST(transfer_quantity AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferQuantity,
    ISNULL((SELECT SUM(TRY_CAST(transfer_amount AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS transferAmount
FROM wms_knzy_transfer_order tor
LEFT JOIN wms_basic_warehouse outWh ON TRY_CAST(tor.outwhse AS INT) = outWh.warehouse_id
LEFT JOIN wms_basic_warehouse inWh ON TRY_CAST(tor.inwhse AS INT) = inWh.warehouse_id
LEFT JOIN sys_org_user_management handler ON TRY_CAST(tor.handler_id AS INT) = handler.user_id
LEFT JOIN sys_org_user_management maker ON tor.create_by = maker.user_account
LEFT JOIN sys_org_organizations dept ON TRY_CAST(maker.organization_id AS INT) = dept.organization_id
WHERE tor.transfer_order_id = @Id AND tor.is_delete = 0";

            try
            {
                CommandDefinition command;
                if (transaction != null)
                {
                    // 关键修复：在事务中查询时，使用CommandDefinition传递事务
                    command = new CommandDefinition(sql, new { Id = id }, transaction: transaction);
                }
                else
                {
                    // 非事务查询
                    EnsureConnectionOpen();
                    command = new CommandDefinition(sql, new { Id = id }, commandTimeout: 30);
                }

                var order = await _dbConnection.QueryFirstOrDefaultAsync<TransferOrderDTO>(command);
                if (order != null)
                {
                    // 调用带事务的明细查询
                    order.details = await GetDetailsByOrderIdAsync(id, transaction);
                }
                return order;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询调拨单{id}详情失败{(transaction != null ? "（事务内）" : "")}");
                throw;
            }
            finally
            {
                // 只有在非事务场景下才关闭连接
                if (transaction == null)
                {
                    EnsureConnectionClosed();
                }
            }
        }
        /// <summary>
        /// 带事务的调拨单明细查询（核心修复）
        /// </summary>
        public async Task<List<TransferOrderDetailDTO>> GetDetailsByOrderIdAsync(int transferOrderId, DbTransaction transaction)
        {
            var sql = @"
SELECT 
    td.product_code AS product_code,
    ISNULL(p.product_name, td.product_code) AS product_name,
    ISNULL(p.specification_model, '') AS specification_model,
    ISNULL(p.product_type_id, '') AS product_type,
    TRY_CAST(td.transfer_quantity AS INT) AS transfer_quantity,
    TRY_CAST(td.iprice AS DECIMAL(18,2)) AS in_price,
    TRY_CAST(td.transfer_amount AS DECIMAL(18,2)) AS transfer_amount
FROM wms_knzy_transfer_details td
LEFT JOIN wms_basic_product_information p ON td.product_code = p.product_code
WHERE td.transfer_order_id = @TransferOrderId";

            try
            {
                var command = new CommandDefinition(
                    sql,
                    new { TransferOrderId = transferOrderId },
                    transaction: transaction);
                var details = await _dbConnection.QueryAsync<TransferOrderDetailDTO>(command);
                return details.AsList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询调拨单{transferOrderId}明细失败（事务内）");
                throw;
            }
        }
        public async Task<bool> AddAsync(TransferOrderCreateDTO dto)
        {
            if (dto == null) throw new ArgumentNullException(nameof(dto));
            if (dto.details == null || dto.details.Count == 0)
                throw new ArgumentException("调拨单明细不能为空");

            IDbTransaction transaction = null;
            var wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
            try
            {
                if (!wasConnectionOpen)
                    _dbConnection.Open();

                transaction = _dbConnection.BeginTransaction();

                var mainSql = @"
INSERT INTO wms_knzy_transfer_order (
    transno_no, transfer_type, tfrrqdt, outwhse, inwhse,
    whloc, trwareloc, handler_id, note, document_status,
    outbound_status, storage_status, create_by, create_time,
    update_by, update_time, organizations_id, reviewer,
    review_time, is_delete
) VALUES (
    @transnoNo, @transferType, @tfrrqdt, @outWarehouseId, @inWarehouseId,
    @whloc, @trwareloc, @handlerId, @note, @documentStatus,
    @outboundStatus, @storageStatus, @createBy, @createTime,
    @updateBy, @updateTime, @organizations_id, @reviewer,
    @review_time, @is_delete
);
SELECT CAST(SCOPE_IDENTITY() AS INT);";

                var transferOrderId = await _dbConnection.ExecuteScalarAsync<int>(mainSql, new
                {
                    dto.transnoNo,
                    dto.transferType,
                    dto.tfrrqdt,
                    outWarehouseId = dto.outWarehouseId.ToString(),
                    inWarehouseId = dto.inWarehouseId.ToString(),
                    dto.whloc,
                    dto.trwareloc,
                    dto.handlerId,
                    note = dto.note ?? "",
                    dto.documentStatus,
                    dto.outboundStatus,
                    dto.storageStatus,
                    dto.createBy,
                    dto.createTime,
                    dto.updateBy,
                    dto.updateTime,
                    dto.organizations_id,
                    dto.reviewer,
                    dto.review_time,
                    dto.is_delete
                }, transaction);

                var detailSql = @"
INSERT INTO wms_knzy_transfer_details (
    transfer_order_id, product_code, transfer_quantity, iprice,        
    note, create_by, create_time, update_by, update_time, is_delete,
    transfer_amount
) VALUES (
    @transfer_order_id, @product_code, @transfer_quantity, @iprice,       
    @note, @create_by, @create_time, @update_by, @update_time, @is_delete,
    @transfer_amount
)";

                foreach (var detail in dto.details)
                {
                    if (detail.transfer_quantity <= 0)
                        throw new ArgumentException($"货品{detail.product_code}：调拨数量必须大于0");
                    if (detail.in_price < 0)
                        throw new ArgumentException($"货品{detail.product_code}：入库单价不能为负数");

                    var transferAmount = Math.Round(detail.transfer_quantity * detail.in_price, 2);
                    await _dbConnection.ExecuteAsync(detailSql, new
                    {
                        transfer_order_id = transferOrderId,
                        detail.product_code,
                        detail.transfer_quantity,
                        iprice = detail.in_price,
                        note = detail.note ?? "",
                        create_by = dto.createBy,
                        create_time = dto.createTime,
                        update_by = dto.updateBy,
                        update_time = dto.updateTime,
                        is_delete = 0,
                        transfer_amount = transferAmount
                    }, transaction);
                }

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                _logger.LogError(ex, $"创建调拨单{dto.transnoNo}失败");
                return false;
            }
            finally
            {
                if (!wasConnectionOpen && _dbConnection.State == ConnectionState.Open)
                    _dbConnection.Close();
            }
        }

        public async Task<List<TransferOrderDetailDTO>> GetDetailsByOrderIdAsync(int transferOrderId)
        {
            var sql = @"
SELECT 
    td.product_code AS product_code,
    ISNULL(p.product_name, td.product_code) AS product_name,
    ISNULL(p.specification_model, '') AS specification_model,
    ISNULL(p.product_type_id, '') AS product_type,
    TRY_CAST(td.transfer_quantity AS INT) AS transfer_quantity,
    TRY_CAST(td.iprice AS DECIMAL(18,2)) AS in_price,
    TRY_CAST(td.transfer_amount AS DECIMAL(18,2)) AS transfer_amount
FROM wms_knzy_transfer_details td
LEFT JOIN wms_basic_product_information p ON td.product_code = p.product_code
WHERE td.transfer_order_id = @TransferOrderId";

            try
            {
                EnsureConnectionOpen();
                var details = await _dbConnection.QueryAsync<TransferOrderDetailDTO>(sql, new { TransferOrderId = transferOrderId });
                return details.AsList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询调拨单{transferOrderId}明细失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }

        public async Task<bool> AuditAsync(int transferOrderId, string auditor, string auditResult)
        {
            if (transferOrderId <= 0) throw new ArgumentException("调拨单ID无效");
            if (string.IsNullOrEmpty(auditor)) throw new ArgumentException("审核人不能为空");
            if (!new[] { "3", "2" }.Contains(auditResult)) throw new ArgumentException("审核结果只能是3(通过)或2(驳回)");

            var checkSql = "SELECT document_status FROM wms_knzy_transfer_order WHERE transfer_order_id = @Id";
            EnsureConnectionOpen();
            var currentStatus = await _dbConnection.QueryFirstOrDefaultAsync<string>(checkSql, new { Id = transferOrderId });
            if (currentStatus != "1")
            {
                _logger.LogWarning($"审核失败：调拨单{transferOrderId}状态为{currentStatus}（非待审核）");
                return false;
            }

            var sql = @"
UPDATE wms_knzy_transfer_order 
SET 
    document_status = @auditResult,
    reviewer = @auditor,
    review_time = GETDATE(),
    update_time = GETDATE(),
    update_by = @auditor
WHERE 
    transfer_order_id = @transferOrderId 
    AND document_status = '1';";

            try
            {
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new
                {
                    transferOrderId,
                    auditor,
                    auditResult
                });
                return affectedRows > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"审核调拨单{transferOrderId}失败");
                return false;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }

        public async Task<List<TransferOrderExportDTO>> GetExportDataAsync(
            string transnoNo, string documentStatus, string ioStatus, DateTime? startDate, DateTime? endDate)
        {
            var sql = @"
SELECT 
    tor.transno_no AS TransnoNo,
    CASE tor.document_status 
        WHEN '0' THEN '草稿' WHEN '1' THEN '待审核' WHEN '2' THEN '驳回' WHEN '3' THEN '通过' WHEN '4' THEN '已完成' 
        ELSE '未知' END AS DocumentStatusText,
    CONVERT(VARCHAR(20), tor.tfrrqdt, 23) AS Tfrrqdt,
    tor.transfer_type AS TransferType,
    ISNULL(outWh.warehouse_name, tor.outwhse) AS OutWarehouseName,
    ISNULL(inWh.warehouse_name, tor.inwhse) AS InWarehouseName,
    -- 新增：仓库编码（warehouse_number）
    ISNULL(outWh.warehouse_number, '') AS OutWarehouseCode,
    ISNULL(inWh.warehouse_number, '') AS InWarehouseCode,
    CASE tor.outbound_status 
        WHEN '0' THEN '未出库' WHEN '1' THEN '部分出库' WHEN '2' THEN '已出库' ELSE '未知' END AS OutboundStatusText,
    CASE tor.storage_status 
        WHEN '0' THEN '未入库' WHEN '1' THEN '部分入库' WHEN '2' THEN '已入库' ELSE '未知' END AS StorageStatusText,
    ISNULL((SELECT SUM(TRY_CAST(transfer_quantity AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferQuantity,
    ISNULL((SELECT SUM(TRY_CAST(transfer_amount AS DECIMAL(18,2))) 
            FROM wms_knzy_transfer_details 
            WHERE transfer_order_id = tor.transfer_order_id), 0) AS TransferAmount,
    ISNULL(handler.user_name, tor.handler_id) AS HandlerName,
    ISNULL((SELECT TOP 1 ISNULL(p.product_name, td.product_code) 
            FROM wms_knzy_transfer_details td
            LEFT JOIN wms_basic_product_information p ON td.product_code = p.product_code
            WHERE td.transfer_order_id = tor.transfer_order_id), '无货品') AS ProductName,
    ISNULL(maker.user_name, tor.create_by) AS MakerName,
    ISNULL(dept.organization_name, '-') AS DepartmentName,
    CONVERT(VARCHAR(20), tor.create_time, 23) AS CreateTime,
    ISNULL((SELECT user_name FROM sys_org_user_management WHERE user_account = tor.reviewer), ISNULL(tor.reviewer, '-')) AS ReviewerName,
    CONVERT(VARCHAR(20), tor.review_time, 23) AS ReviewTime
FROM wms_knzy_transfer_order tor
LEFT JOIN wms_basic_warehouse outWh ON TRY_CAST(tor.outwhse AS INT) = outWh.warehouse_id  
LEFT JOIN wms_basic_warehouse inWh ON TRY_CAST(tor.inwhse AS INT) = inWh.warehouse_id    
LEFT JOIN sys_org_user_management handler ON TRY_CAST(tor.handler_id AS INT) = handler.user_id
LEFT JOIN sys_org_user_management maker ON tor.create_by = maker.user_account
LEFT JOIN sys_org_organizations dept ON TRY_CAST(maker.organization_id AS INT) = dept.organization_id
WHERE tor.is_delete = 0
";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(transnoNo))
                sql += " AND tor.transno_no LIKE @TransnoNo";
            if (!string.IsNullOrWhiteSpace(documentStatus))
                sql += " AND tor.document_status = @DocumentStatus";
            if (startDate.HasValue)
                sql += " AND tor.tfrrqdt >= @StartDate";
            if (endDate.HasValue)
                sql += " AND tor.tfrrqdt <= @EndDate";
            if (!string.IsNullOrWhiteSpace(ioStatus))
            {
                if (ioStatus.StartsWith("out-"))
                    sql += " AND tor.outbound_status = @OutboundStatus";
                else if (ioStatus.StartsWith("in-"))
                    sql += " AND tor.storage_status = @StorageStatus";
            }

            if (!string.IsNullOrWhiteSpace(transnoNo))
                parameters.Add("@TransnoNo", $"%{transnoNo.Trim()}%");
            if (!string.IsNullOrWhiteSpace(documentStatus))
                parameters.Add("@DocumentStatus", documentStatus.Trim());
            if (startDate.HasValue)
                parameters.Add("@StartDate", startDate.Value.Date);
            if (endDate.HasValue)
                parameters.Add("@EndDate", endDate.Value.Date.AddDays(1).AddTicks(-1));
            if (!string.IsNullOrWhiteSpace(ioStatus))
            {
                if (ioStatus.StartsWith("out-"))
                    parameters.Add("@OutboundStatus", ioStatus.Split('-')[1]);
                else if (ioStatus.StartsWith("in-"))
                    parameters.Add("@StorageStatus", ioStatus.Split('-')[1]);
            }

            try
            {
                EnsureConnectionOpen();
                var result = await _dbConnection.QueryAsync<TransferOrderExportDTO>(sql, parameters);
                return result?.AsList() ?? new List<TransferOrderExportDTO>();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取导出数据失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }

        public async Task<bool> AuditAsync(int transferOrderId, string auditor)
        {
            string defaultAuditResult = "3";
            return await AuditAsync(transferOrderId, auditor, defaultAuditResult);
        }

        public async Task UpdateAsync(TransferOrderDTO transferOrder)
        {
            if (transferOrder == null || transferOrder.TransferOrderId <= 0)
                throw new ArgumentException("调拨单ID无效或数据为空");

            var sql = @"
UPDATE wms_knzy_transfer_order 
SET 
    document_status = @DocumentStatus,
    outbound_status = @OutboundStatus,
    storage_status = @StorageStatus,
    review_time = @ReviewTime,
    update_time = GETDATE(),
    update_by = @UpdateBy  -- SQL中的参数
WHERE 
    transfer_order_id = @TransferOrderId";

            try
            {
                EnsureConnectionOpen();
                await _dbConnection.ExecuteAsync(sql, new
                {
                    transferOrder.TransferOrderId,
                    transferOrder.DocumentStatus,
                    transferOrder.OutboundStatus,
                    transferOrder.StorageStatus,
                    transferOrder.ReviewTime,
                    // 关键修复：传入@UpdateBy对应的参数值（从DTO的UpdateBy属性获取）
                    UpdateBy = transferOrder.UpdateBy ?? "system"  // 若为null，用默认值"system"
                });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"更新调拨单{transferOrder.TransferOrderId}状态失败");
                throw;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }
        /// <summary>
        /// 双参数撤销：同时设置 reviewer 为当前用户、review_time 为当前时间
        /// </summary>
        public async Task<bool> CancelAsync(int transferOrderId, string operatorUser)
        {
            // 校验操作人不为空（避免传入空值导致 reviewer 仍为 NULL）
            if (string.IsNullOrEmpty(operatorUser))
                throw new ArgumentException("操作人不能为空，无法设置 reviewer");

            var sql = @"
UPDATE wms_knzy_transfer_order 
SET 
    document_status = '1',  -- 待审核状态
    outbound_status = '0',  -- 未出库
    storage_status = '0',   -- 未入库
    reviewer = @Operator,   -- 修正：reviewer 设为当前操作用户（非空）
    review_time = GETDATE(),-- 修正：review_time 设为当前时间（非空）
    update_by = @Operator,  -- 记录更新操作人
    update_time = GETDATE() -- 记录更新时间
WHERE transfer_order_id = @Id;";

            try
            {
                EnsureConnectionOpen();
                var rowsAffected = await _dbConnection.ExecuteAsync(sql, new
                {
                    Id = transferOrderId,
                    Operator = operatorUser  // 传递当前用户作为参数
                });
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"数据库操作失败：撤销调拨单ID={transferOrderId}，操作人={operatorUser}");
                return false;
            }
            finally
            {
                EnsureConnectionClosed();
            }
        }
        // 新增带事务的重载实现（核心）
        public async Task<bool> AuditAsync(int transferOrderId, string auditor, string auditResult, DbTransaction transaction)
        {
            var sql = @"
UPDATE wms_knzy_transfer_order 
SET 
    document_status = @auditResult,
    reviewer = @auditor,
    review_time = GETDATE(),
    update_time = GETDATE(),
    update_by = @auditor
WHERE 
    transfer_order_id = @transferOrderId 
    AND document_status = '1';";

            var affectedRows = await _dbConnection.ExecuteAsync(sql, new
            {
                transferOrderId,
                auditor,
                auditResult
            }, transaction); // 使用传入的事务
            return affectedRows > 0;
        }

        public async Task UpdateAsync(TransferOrderDTO transferOrder, DbTransaction transaction)
        {
            var sql = @"
UPDATE wms_knzy_transfer_order 
SET 
    document_status = @DocumentStatus,
    outbound_status = @OutboundStatus,
    storage_status = @StorageStatus,
    review_time = @ReviewTime,
    update_time = GETDATE(),
    update_by = @UpdateBy
WHERE 
    transfer_order_id = @TransferOrderId";

            await _dbConnection.ExecuteAsync(sql, new
            {
                transferOrder.TransferOrderId,
                transferOrder.DocumentStatus,
                transferOrder.OutboundStatus,
                transferOrder.StorageStatus,
                transferOrder.ReviewTime,
                UpdateBy = transferOrder.UpdateBy ?? "system"
            }, transaction); // 使用传入的事务
        }

        private void EnsureConnectionOpen()
        {
            if (_dbConnection.State != ConnectionState.Open)
                _dbConnection.Open();
        }

        private void EnsureConnectionClosed()
        {
            if (_dbConnection.State == ConnectionState.Open)
                _dbConnection.Close();
        }
    }
}